/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sk.uniba.fmph.asdf.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import sk.uniba.fmph.asdf.exceptions.DatabaseException;
import sk.uniba.fmph.asdf.exceptions.OfflineException;
import sk.uniba.fmph.asdf.tempt.PrimaryTestFieldDef;
import sk.uniba.fmph.asdf.tempt.TestFieldDef;
import sk.uniba.fmph.asdf.tempt.TestTemplate;
import sk.uniba.fmph.asdf.test.Comment;
import sk.uniba.fmph.asdf.test.RelativePoint;
import sk.uniba.fmph.asdf.test.Stamp;
import sk.uniba.fmph.asdf.test.StampType;
import sk.uniba.fmph.asdf.test.Test;
import sk.uniba.fmph.asdf.test.TestType;

/**
 * Trieda sluzi ako medziclanok medzi MySQL databazou a triedami volajuce SQL kod.
 * @author tim
 * @author Ivor
 */
public class SQLQueryHandler {

	private static final String host =
			"jdbc:mysql://kempelen.ii.fmph.uniba.sk:3306/";
	private static final String db = "testy";
	private static final String username = "testy";
	private static final String password = "VinaliesInternationales2012";
	private static final String query =
			"?useUnicode=yes&characterEncoding=UTF-8";

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;

    /**
     * Vytvorenie spojenia. Vracia vynimku ak sa nepodarilo pripojit k databaze.
     */
	public void openConnection() throws OfflineException {
		try {
			connection =
					DriverManager.getConnection(host + db + query, username,
							password);
			statement = connection.createStatement();
		} catch (SQLException ex) {
			throw new OfflineException();
		}
	}

	/**
	 * Vrati mnozinu udajov ako objekt, v zavislosti od volanej query.
	 *
	 * @param query
	 *            definovane SQL volanie
	 * @param args
	 *            argumenty, s ktorymi sa SQL volanie vykona
	 */
	public Object getResultSet(SQLQuery query, Object... args)
			throws DatabaseException {
		executeQuery(query, args);
		try {
			switch (query) {
			case GET_USER:
				return getUser();
			case GET_USER_BY_USERNAME:
				return getUser();
			case GET_USERS:
				return getUsers();
			case LOGIN_USER:
				return getUser();
			case GET_EVALUATORS:
				return getUsers();
			case GET_FIELDS:
				return getFieldDefinitions();
			case GET_TEMPLATE:
				return getTestTemplate();
			case GET_MODIFIED_TESTS:
				return getAvailableModifiedTests();
			case GET_STAMPS:
				return getStamps();
			case GET_FIELD_EXTENSIONS:
				return getFieldExtensions();
			case GET_LOGS:
				return getLogs();
			default:
				throw new DatabaseException();
			}
		} catch (SQLException ex) {
			throw new DatabaseException(ex.getMessage());
		}
	}

	/**
	 * Vykonanie SQL kodu s danymi parametrami.
	 *
	 * @param query
	 *            definovane SQL volanie
	 * @param args
	 *            argumenty, s ktorymi sa SQL volanie vykona
	 */
	private void executeQuery(SQLQuery query, Object... args)
			throws DatabaseException {
		try {
			resultSet = statement.executeQuery(String.format(query.toString(), args));
		} catch (SQLException ex) {
			throw new DatabaseException(ex.getMessage());
		}
	}

    /**
     * Vrati uzivatela z vykonanej SQL query.
     */
	private Account getUser() throws SQLException, DatabaseException {
		if (resultSet.isLast()) {
			throw new DatabaseException("User not found");
		}
		skipResultIfBeforeFirst();
		return new Account(resultSet.getInt("id"),
				resultSet.getString("username"), new FullName(
						resultSet.getString("first_name"),
						resultSet.getString("last_name")));
	}

    /**
     * Vrati zoznam uzivatelov z vykonanej SQL query.
     */
	private ArrayList<Account> getUsers() throws SQLException,
			DatabaseException {
		ArrayList<Account> users = new ArrayList<>();
		while (resultSet.next()) {
			Account user =
					new Account(resultSet.getInt("id"),
							resultSet.getString("username"), new FullName(
									resultSet.getString("first_name"),
									resultSet.getString("last_name")));
			users.add(user);
		}
		return users;
	}

	/**
	 * Vrati zoznam testov, ku ktorym ma pouzivatel pristup z vykonanej SQL
	 * query.
	 *
	 * @return Zoznam testov, ku ktorym ma pouzivatel pristup.
	 * @throws SQLException
	 *             Ak nastala chyba v spojeni s databazou alebo so spracovanim
	 *             dotazu.
	 * @throws DatabaseException Ak nastala chyba pri ziskavani pomocnych udajov.
	 */
	private ArrayList<Test> getAvailableModifiedTests() throws SQLException, DatabaseException {
		ArrayList<Test> tests = new ArrayList<>();
		while (resultSet.next()) {
			int testId = resultSet.getInt("id");
			int testType = resultSet.getInt("test_type");
			String studentName = resultSet.getString("student_name");
			int evaluatorId = resultSet.getInt("user_id");

			// Pomocny SQLQueryHandler pre ziskanie pouzivatela
			SQLQueryHandler helpHandler = new SQLQueryHandler();
			try {
				helpHandler.openConnection();
			} catch (OfflineException e) {
				e.printStackTrace();
			}
			Account evaluator =
					(Account) helpHandler.getResultSet(SQLQuery.GET_USER,
							evaluatorId);
			helpHandler.closeConnection();

			Test test =
					new Test(testId, TestType.values()[testType], studentName,
							evaluator, null);
			tests.add(test);
		}
		return tests;
	}

	/**
	 * Vrati zoznam rozsireni oblasti testu.
	 *
	 * @return Zoznam rozsireni oblasti testu.
	 * @throws SQLException
	 *             Ak nastala chyba v spojeni s databazou alebo so spracovanim
	 *             dotazu.
	 */
	private ArrayList<TestFieldDef> getFieldExtensions() throws SQLException {
		ArrayList<TestFieldDef> fields = new ArrayList<>();
		while (resultSet.next()) {
			int fieldId = resultSet.getInt("id");
			int parentId = resultSet.getInt("parent_id");
			float posX = resultSet.getFloat("pos_x");
			float posY = resultSet.getFloat("pos_y");
			float width = resultSet.getFloat("width");
			float height = resultSet.getFloat("height");
			RelativePoint topLeft = new RelativePoint(posX, posY);
			RelativePoint bottomRight = new RelativePoint(posX + width, posY + height);
			fields.add(new TestFieldDef(fieldId, parentId, topLeft, bottomRight));
		}
		return fields;
	}

	/**
	 * Vrati zoznam peciatok, ku ktorym ma pouzivatel pristup z vykonanej SQL
	 * query.
	 *
	 * @return Zoznam peciatok, ku ktorym ma pouzivatel pristup.
	 * @throws SQLException
	 *             Ak nastala chyba v spojeni s databazou alebo so spracovanim
	 *             dotazu.
	 */
	private ArrayList<Stamp> getStamps() throws SQLException {
		ArrayList<Stamp> stamps = new ArrayList<>();
		while (resultSet.next()) {
			int id = resultSet.getInt("id");
			float posX = resultSet.getFloat("pos_x");
			float posY = resultSet.getFloat("pos_y");
			StampType stampType =
					StampType.values()[resultSet.getInt("stamp_type")];
			if (stampType == StampType.COMMENT) {
				stamps.add(new Comment(id, posX, posY, resultSet.getString("comment")));
			} else {
				stamps.add(new Stamp(id, posX, posY, stampType));
			}
		}
		return stamps;
	}

	/**
	 * Vrati sablonu.
	 *
	 * @return Sablona.
	 * @throws SQLException
	 *             Ak nastala chyba v spojeni s databazou alebo so spracovanim
	 *             dotazu.
	 * @throws DatabaseException
	 *             Ak nastala chyba pri ziskavani pomocnych udajov.
	 */
	@SuppressWarnings("unchecked")
	private TestTemplate getTestTemplate() throws SQLException,
			DatabaseException {
		skipResultIfBeforeFirst();
		// Ziskame zakladne udaje sablony
		int templateId = resultSet.getInt("id");
		String templateName = resultSet.getString("name");
		int authorId = resultSet.getInt("author_id");
		Account authorAccount = null;

		// Ziskame ucet autora sablony
		ArrayList<Account> allUsers =
				(ArrayList<Account>) getResultSet(SQLQuery.GET_USERS);
		for (Account account : allUsers) {
			if (account.getId() == authorId) {
				authorAccount = account;
				break;
			}
		}

		// Ziskame definicie oblasti sablony
		ArrayList<PrimaryTestFieldDef> fieldDefs =
				(ArrayList<PrimaryTestFieldDef>) getResultSet(
						SQLQuery.GET_FIELDS, templateId);

		// Ziskame zoznam hodnotitelov
		ArrayList<Account> evaluatorAccounts =
				(ArrayList<Account>) getResultSet(SQLQuery.GET_EVALUATORS,
						templateId);

		return new TestTemplate(templateId, templateName, authorAccount,
				fieldDefs, evaluatorAccounts);
	}

	/**
	 * Vrati zoznam definicii oblasti testu.
	 *
	 * @return Zoznam definicii oblasti testu.
	 * @throws SQLException
	 *             Ak nastala chyba v spojeni s databazou alebo so spracovanim
	 *             dotazu.
	 */
	private ArrayList<PrimaryTestFieldDef> getFieldDefinitions()
			throws SQLException {
		ArrayList<PrimaryTestFieldDef> fieldDefs = new ArrayList<>();
		while (resultSet.next()) {
			int id = resultSet.getInt("id");
			float posX = resultSet.getFloat("pos_x");
			float posY = resultSet.getFloat("pos_y");
			float width = resultSet.getFloat("width");
			float height = resultSet.getFloat("height");
			int maxPoints = resultSet.getInt("points_max");
			RelativePoint topLeft = new RelativePoint(posX, posY);
			RelativePoint bottomRight = new RelativePoint(posX + width, posY + height);
			fieldDefs.add(new PrimaryTestFieldDef(id, topLeft, bottomRight,
					maxPoints));
		}
		return fieldDefs;
	}

	/**
	 * Pomocna trieda pre uchovavanie zaznamu cinnosti nad databazou. Obsahuje
	 * spravu zaznamu a datum a cas, kedy sa cinnsot vykonala.
	 *
	 * @author Ivor
	 */
	public class MessageDatePair {
		public String message;
		public Timestamp date;

		public MessageDatePair(final String message, final Timestamp date) {
			this.message = message;
			this.date = date;
		}
	}

	/**
	 * Vrati zoznam zaznamov cinnosti nad databazou.
	 *
	 * @return Zoznam zaznamov cinnosti nad databazou.
	 * @throws SQLException
	 *             Ak nastala chyba v spojeni s databazou alebo so spracovanim
	 *             dotazu.
	 */
	private ArrayList<MessageDatePair> getLogs() throws SQLException {
		ArrayList<MessageDatePair> logs = new ArrayList<>();
		while (resultSet.next()) {
			String message = resultSet.getString("message");
			Timestamp date = resultSet.getTimestamp("log_date");
			logs.add(new MessageDatePair(message, date));
		}
		return logs;
	}

	/**
	 * Aktualizovanie databazy s danou SQL query a argumentami.
	 *
	 * @param query
	 *            Definovane SQL volanie.
	 * @param args
	 *            Argumenty, s ktorymi sa SQL volanie vykona.
	 */
	public int executeUpdate(SQLQuery query, Object... args)
			throws DatabaseException {
		try {
			return statement
					.executeUpdate(String.format(query.toString(), args));
		} catch (SQLException ex) {
			throw new DatabaseException(ex.getMessage());
		}
	}

	/**
	 * Aktualizovanie databazy s danou SQL query a argumentami, ktore vracia ID vlozeneho zaznamu, ak taky bol.
	 *
	 * @param query
	 *            definovane SQL volanie
	 * @param args
	 *            argumenty, s ktorymi sa SQL volanie vykona
	 *            @return ID vlozeneho zaznamu
	 */
	public int executeUpdateReturningId(SQLQuery query, Object... args)
			throws DatabaseException {
		try {
			resultSet = statement.executeQuery(String.format(query.toString(), args));
			skipResultIfBeforeFirst();
			return resultSet.getInt(1);
		} catch (SQLException ex) {
			throw new DatabaseException(ex.getMessage());
		}
	}

    /**
     * Ukoncenie spojenia s databazou.
     */
	public void closeConnection() {
		try {
			connection.close();
		} catch (SQLException ex) {
			System.out.println("Unable to disconnect.");
		}
	}

	private void skipResultIfBeforeFirst() {
		try {
			if (resultSet.isBeforeFirst()) {
				resultSet.next();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}